package cn.com.demo.poi;

import org.apache.poi.ss.usermodel.*;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

import static org.apache.logging.log4j.util.Strings.isEmpty;

public class ExcelComparator {
    static String sourceFile = "";
    static String targetFile = "";
    static String outFile = "d:\\out.txt";

    public static void main(String[] args) throws IOException {
        if (args != null && args.length == 3) {
            // 启动参数传入
            sourceFile = args[0];
            targetFile = args[1];
            outFile = args[2];
        } else {
            // 运行态参数处理
            Scanner scanner = new Scanner(System.in);
            System.out.print("请输入第一个Excel的完整路径：");
            sourceFile = scanner.nextLine(); // 从标准输入流读取整数
            System.out.print("请输入第二个Excel的完整路径：");
            targetFile = scanner.nextLine(); // 从标准输入流读取整数
            System.out.print("请输入结果文件输出的完整路径：");
            outFile = scanner.nextLine(); // 从标准输入流读取整数
        }
        if (isEmpty(sourceFile) || isEmpty(targetFile) || isEmpty(outFile)) {
            System.out.println("输入有误，请重新运行！");
            System.exit(-1);
        }
        FileInputStream file1Input = new FileInputStream(new File(sourceFile));
        FileInputStream file2Input = new FileInputStream(new File(targetFile));

        Workbook workbook1 = WorkbookFactory.create(file1Input);
        Workbook workbook2 = WorkbookFactory.create(file2Input);

        List<String[]> differences = compareWorkbooks(workbook1, workbook2);

        if (differences.isEmpty()) {
            System.out.println("两个Excel一毛一样，没有差异.");
        } else {
            FileOutputStream out = new FileOutputStream(outFile);
            for (String[] diff : differences) {
                out.write(("【" + diff[0] + "】 - 【" + diff[1] + "】 - " + diff[2] + "\n").getBytes());
            }
            System.out.println("两个Excel存在差异:" + outFile);
            out.close();
        }

        file1Input.close();
        file2Input.close();
    }

    public static List<String[]> compareWorkbooks(Workbook workbook1, Workbook workbook2) {
        List<String[]> differences = new ArrayList<>();

        int numberOfSheets1 = workbook1.getNumberOfSheets();
        int numberOfSheets2 = workbook2.getNumberOfSheets();
        int numberOfSheets = Math.max(numberOfSheets1, numberOfSheets2);

        for (int i = 0; i < numberOfSheets; i++) {
            Sheet sheet1 = i < numberOfSheets1 ? workbook1.getSheetAt(i) : null;
            Sheet sheet2 = i < numberOfSheets2 ? workbook2.getSheetAt(i) : null;

            if (sheet1 == null) {
                String[] diff = {"Sheet页缺失", sheet2.getSheetName(), "Sheet页 " + sheet2.getSheetName() + " 不在第一个Excel文件中"};
                differences.add(diff);
                continue;
            } else if (sheet2 == null) {
                String[] diff = {"Sheet页缺失", sheet1.getSheetName(), "Sheet页" + sheet1.getSheetName() + " 不在第二个Excel文件中"};
                differences.add(diff);
                continue;
            }

            List<String[]> sheetDifferences = compareSheets(sheet1, sheet2);
            differences.addAll(sheetDifferences);
        }

        return differences;
    }

    public static List<String[]> compareSheets(Sheet sheet1, Sheet sheet2) {
        List<String[]> differences = new ArrayList<>();

        int numberOfRows1 = sheet1.getPhysicalNumberOfRows();
        int numberOfRows2 = sheet2.getPhysicalNumberOfRows();
        int numberOfRows = Math.max(numberOfRows1, numberOfRows2);

        for (int i = 0; i < numberOfRows; i++) {
            Row row1 = i < numberOfRows1 ? sheet1.getRow(i) : null;
            Row row2 = i < numberOfRows2 ? sheet2.getRow(i) : null;

            if (row1 == null) {
                String[] diff = {"Sheet页：" + sheet1.getSheetName(), "行缺失", "行 " + (i + 1) + " 不在第一个Excel中"};
                differences.add(diff);
                continue;
            } else if (row2 == null) {
                String[] diff = {"Sheet页：" + sheet2.getSheetName(), "行缺失", "行 " + (i + 1) + " 不在第二个Excel中"};
                differences.add(diff);
                continue;
            }

            List<String[]> rowDifferences = compareRows(row1, row2, i, sheet1);
            differences.addAll(rowDifferences);
        }

        return differences;
    }

    public static List<String[]> compareRows(Row row1, Row row2, int rowIndex, Sheet sheet1) {
        List<String[]> differences = new ArrayList<>();

        int numberOfCells1 = row1.getPhysicalNumberOfCells();
        int numberOfCells2 = row2.getPhysicalNumberOfCells();
        int numberOfCells = Math.max(numberOfCells1, numberOfCells2);

        for (int j = 0; j < numberOfCells; j++) {
            Cell cell1 = j < numberOfCells1 ? row1.getCell(j) : null;
            Cell cell2 = j < numberOfCells2 ? row2.getCell(j) : null;

            if (cell1 == null || cell2 == null) {
                if (cell1 == null && cell2 == null) {
                    continue; // Both cells are empty, no need to compare
                } else {
                    String[] diff = new String[3];
                    diff[0] = "Sheet页：" + sheet1.getSheetName();
                    diff[1] = "行： " + (rowIndex + 1);
                    diff[2] = "列 " + (j + 1) + (cell1 == null ? "在第一个Excel" : "在第二个Excel") + " 为空，在另一个Excel中不为空";
                    differences.add(diff);
                    continue;
                }
            }

            compareCellValues(cell1, cell2, rowIndex, j, differences, sheet1);
        }

        return differences;
    }

    private static void compareCellValues(Cell cell1, Cell cell2, int rowIndex, int cellIndex, List<String[]> differences, Sheet sheet1) {
        if (cell1.getCellType() != cell2.getCellType()) {
            String[] diff = new String[3];
            diff[0] = "Sheet页：" + sheet1.getSheetName();
            diff[1] = "行 " + (rowIndex + 1);
            diff[2] = "列 " + (cellIndex + 1) + " 数据类型不一致: " + cell1.getCellType().name() + " != " + cell2.getCellType().name() + " val: -> " + getCellStringVal(cell1) + " != " + getCellStringVal(cell2);
            differences.add(diff);
            return;
        }

        if (cell1.getCellType() == CellType.STRING) {
            if (!cell1.getStringCellValue().equals(cell2.getStringCellValue())) {
                String[] diff = new String[3];
                diff[0] = sheet1.getSheetName();
                diff[1] = "Row " + (rowIndex + 1);
                diff[2] = "Cell " + (cellIndex + 1) + ": " + cell1.getStringCellValue() + " != " + cell2.getStringCellValue();
                differences.add(diff);
            }
        } else if (cell1.getCellType() == CellType.NUMERIC) {
            if (cell1.getNumericCellValue() != cell2.getNumericCellValue()) {
                String[] diff = new String[3];
                diff[0] = sheet1.getSheetName();
                diff[1] = "Row " + (rowIndex + 1);
                diff[2] = "Cell " + (cellIndex + 1) + ": " + cell1.getNumericCellValue() + " != " + cell2.getNumericCellValue();
                differences.add(diff);
            }
        } else if (cell1.getCellType() == CellType.BOOLEAN) {
            if (cell1.getBooleanCellValue() != cell2.getBooleanCellValue()) {
                String[] diff = new String[3];
                diff[0] = sheet1.getSheetName();
                diff[1] = "Row " + (rowIndex + 1);
                diff[2] = "Cell " + (cellIndex + 1) + ": " + cell1.getBooleanCellValue() + " != " + cell2.getBooleanCellValue();
                differences.add(diff);
            }
        } else if (cell1.getCellType() == CellType.FORMULA) {
            if (!cell1.getCellFormula().equals(cell2.getCellFormula())) {
                String[] diff = new String[3];
                diff[0] = sheet1.getSheetName();
                diff[1] = "Row " + (rowIndex + 1);
                diff[2] = "Cell " + (cellIndex + 1) + ": " + cell1.getCellFormula() + " != " + cell2.getCellFormula();
                differences.add(diff);
            }
        }
    }

    public static String getCellStringVal(Cell cell) {
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case BOOLEAN:
                return cell.getBooleanCellValue()+"";
            case NUMERIC:
                return cell.getNumericCellValue()+"";
            case _NONE:
                return "_NONE";
            case FORMULA:
                return cell.getCellFormula();
            case BLANK:
                return "";
            case ERROR:
                return cell.getErrorCellValue() +"";
        }
        return "ERROR_CELLTYPE!";
    }
}